CREATE PROCEDURE [dbo].[asi_CreateInstituteGroupMember]
    @contactKey uniqueidentifier,
    @companyGroupKey uniqueidentifier,
    @createdByUserKey uniqueidentifier,
    @primaryContact bit
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @groupMemberKey uniqueidentifier
    DECLARE @groupRoleKey uniqueidentifier
    DECLARE @defaultMemberStatusCode nvarchar(4)
    DECLARE @now datetime

    SET @groupMemberKey = newid()
    SET @now = getdate()

    -- Get the default role and member status
    IF @primaryContact = 1
    BEGIN
        SELECT @groupRoleKey = GR.GroupRoleKey, @defaultMemberStatusCode = GTR.DefaultMemberStatusCode
        FROM GroupTypeRole GTR INNER JOIN GroupRoleRef GR ON GTR.GroupRoleKey = GR.GroupRoleKey
        WHERE GR.GroupRoleDesc = 'Primary Contact' AND GTR.IsSystem = 1
    END
    ELSE
    BEGIN
        SELECT @groupRoleKey = GTR.GroupRoleKey, @defaultMemberStatusCode = GTR.DefaultMemberStatusCode
        FROM GroupTypeRole GTR INNER JOIN GroupMain GM ON GM.GroupTypeKey = GTR.GroupTypeKey
        WHERE GM.GroupKey = @companyGroupKey AND GTR.IsDefault = 1
    END

    -- Create GroupMember record
    INSERT INTO [GroupMember] (GroupMemberKey, GroupKey, MemberContactKey, IsActive, CreatedByUserKey, CreatedOn,
                               UpdatedByUserKey, UpdatedOn)
    VALUES (@groupMemberKey, @companyGroupKey, @contactKey, 1, @createdByUserKey, @now, @createdByUserKey, @now)

    -- Create GroupMemberDetail record
    INSERT INTO [GroupMemberDetail] (GroupMemberDetailKey, GroupMemberKey, GroupKey, GroupRoleKey, EffectiveDate,
                                     CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn, GroupMemberStatusCode, IsActive)
    VALUES (newid(), @groupMemberKey, @companyGroupKey, @groupRoleKey, @now, @createdByUserKey, @now, @createdByUserKey, @now,
            @defaultMemberStatusCode, 1)


END

GO